package me.keiwu.task.dao;

import me.keiwu.task.domain.Challenge;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.List;

/**
 * Created by kei on 13/11/2017.
 */
public interface ChallengeDao extends JpaRepository<Challenge, Long>, JpaSpecificationExecutor<Challenge> {


    @Query(value = "select * from challenge where (initiator=?1 or challenger=?1) " +
            "and state<2 and now() > start_date and now() < end_date and is_del=0", nativeQuery = true)
    List<Challenge> findRelatedStartingChallenge(Long userId);

    @Query(value = "select * from challenge where (initiator=?1 or challenger=?1) " +
            "and (now() > end_date or state=4) and is_awarded=0 and is_del=0", nativeQuery = true)
    List<Challenge> findAllNotAward(Long userId);



    @Query(value = "select * from challenge where id in (?1)", nativeQuery = true)
    List<Challenge> findAllByIdList(List<Long> idList);


    @Query(value = "select count(*) from challenge where (initiator=?1 or challenger=?1) and state > 0 and state < 3", nativeQuery = true)
    Integer countAllByUserId(Long userId);


    @Query(value = "select count(*) from challenge where (initiator=?1 or challenger=?1) and state = 1", nativeQuery = true)
    Integer countStartingByUserId(Long userId);

    @Query(value = "select count(*) from challenge where (initiator=?1 and challenger=?2) or (initiator=?2 and challenger=?1)", nativeQuery = true)
    Integer countByUserIdAndOtherId(Long userId, Long otherId);





    @Query(value = "select * from challenge where ((initiator=?1 and challenger=?2) or (initiator=?2 and challenger=?1)) " +
            "and state<2 and now() > start_date and now() < end_date and is_del=0", nativeQuery = true)
    List<Challenge> findRelatedStartingChallenge(Long userId, Long otherId);

    @Query(value = "select * from challenge where DATEDIFF(end_date,NOW())=-1 and state=2 \n-- #pageable\n",
           countQuery = "select count(*) from challenge where DATEDIFF(end_date,NOW())=-1 and state=2",
            nativeQuery = true)
    Page<Challenge> findYesterdayChallenge(Pageable pageable);

}
